package cn.jhc.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import cn.jhc.bean.ShopBean;
import cn.jhc.bean.UserBean;

public class TitleDAO {
	
	private Connection con = null;
	
	public UserBean findUser(String username, String password) {
		String sql = "select id,name,password from user "
					+ "where name=? "
					+ " and password=password(?)";
		PreparedStatement stat = null;
		ResultSet result = null;
		UserBean user = null;
		try {
			stat = con.prepareStatement(sql);
			stat.setString(1, username);
			stat.setString(2, password);
			result = stat.executeQuery();
			if(result.next()) {
				user = new UserBean();
				user.setId(result.getInt(1));
				user.setName(result.getString(2));
				user.setPassword(result.getString(3));
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				stat.close();

			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return user;
	}
	
	public UserBean findAdminUser(String username, String password) {
		String sql = "select id,name,password from adminuser "
					+ "where name=? "
					+ " and password=password(?)";
		PreparedStatement stat = null;
		ResultSet result = null;
		UserBean user = null;
		try {
			stat = con.prepareStatement(sql);
			stat.setString(1, username);
			stat.setString(2, password);
			result = stat.executeQuery();
			if(result.next()) {
				user = new UserBean();
				user.setId(result.getInt(1));
				user.setName(result.getString(2));
				user.setPassword(result.getString(3));
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				stat.close();

			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return user;
	}
	
	public List<ShopBean> getAllTitles(){
		List<ShopBean> list = new ArrayList<ShopBean>();
		String sql = "select isbn,title,price,imageFile "
				+ "from shop";
		ResultSet result = null;
		Statement stat = null;
		try {
			stat = con.createStatement();
			result = stat.executeQuery(sql);
			while(result.next()) {
				ShopBean s = new ShopBean();
				s.setIsbn(result.getString(1));
				s.setTitle(result.getString(2));
				s.setPrice(result.getDouble(3));
				s.setImageFile(result.getString(4));
				list.add(s);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally {
			if(stat != null) {
				try {
					stat.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
				stat=null;
			}
			try {
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return list;
	}
	public ShopBean getTitle(String isbn) {
		ShopBean t = new ShopBean();
		String sql = "select isbn,title,imageFile,price "
				+ "from shop where isbn=?";
		PreparedStatement ps = null;
		ResultSet result = null;
		try {
			ps = con.prepareStatement(sql);
			ps.setString(1, isbn);
			result = ps.executeQuery();
			if(!result.next())
				t = null;
			else {
				t.setIsbn(result.getString(1));
				t.setTitle(result.getString(2));
				t.setImageFile(result.getString(3));
				t.setPrice(result.getDouble(4));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally {
			if(ps != null) {
				try {
					ps.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
				ps = null;
			}
			try {
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return t;
	}
	
	public void setCon(Connection con) {
		this.con = con;
	}
}
